!pip install -U kaleido # kaleido required for saving the plotly figures into static images
Collecting kaleido
Downloading kaleido-0.2.1-py2.py3-none-macosx_10_11_x86_64.whl (85.2 MB)
|████████████████████████████████| 85.2 MB 276 kB/s
Installing collected packages: kaleido
Successfully installed kaleido-0.2.1
!pip install geopandas --quiet
!pip install geoplot --quiet
!pip install watermark
%load_ext watermark
%watermark -d -m -v -p numpy,matplotlib,sklearn,pandas
Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/ Collecting watermark Downloading watermark-2.3.1-py2.py3-none-any.whl (7.2 kB) Requirement already satisfied: ipython in /usr/local/lib/python3.7/dist-packages (from watermark) (5.5.0) Requirement already satisfied: importlib-metadata>=1.4 in /usr/local/lib/python3.7/dist-packages (from watermark) (4.11.4) Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=1.4->watermark) (3.8.0) Requirement already satisfied: typing-extensions>=3.6.4 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=1.4->watermark) (4.1.1) Requirement already satisfied: decorator in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (4.4.2) Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (5.1.1) Requirement already satisfied: pygments in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (2.6.1) Requirement already satisfied: prompt-toolkit<2.0.0,>=1.0.4 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (1.0.18) Requirement already satisfied: simplegeneric>0.8 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (0.8.1) Requirement already satisfied: pickleshare in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (0.7.5) Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (57.4.0) Requirement already satisfied: pexpect in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (4.8.0) Requirement already satisfied: wcwidth in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.0.0,>=1.0.4->ipython->watermark) (0.2.5) Requirement already satisfied: six>=1.9.0 in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.0.0,>=1.0.4->ipython->watermark) (1.15.0) Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.7/dist-packages (from pexpect->ipython->watermark) (0.7.0) Installing collected packages: watermark Successfully installed watermark-2.3.1 Python implementation: CPython Python version : 3.7.13 IPython version : 5.5.0 numpy : 1.21.6 matplotlib: 3.2.2 sklearn : 0.0 pandas : 1.3.5 Compiler : GCC 7.5.0 OS : Linux Release : 5.4.188+ Machine : x86_64 Processor : x86_64 CPU cores : 2 Architecture: 64bit
#@title ## Base imports
import os
import sys
import numpy as np
import scipy
import sklearn
import sklearn.linear_model
import pandas as pd
import plotly.express as px
#@title ## Option 1) Mount google drive and import my code
mountpoint_folder_name = "gdrive" # can be anything, doesn't have to be "drive"
project_path_within_drive = "PythonProjects/GeospatialAnalysis" #@param {type:"string"}
project_path_full = os.path.join("/content/",mountpoint_folder_name,
"MyDrive",project_path_within_drive)
try:
import google.colab.drive
import os, sys
# Need to move out of google drive directory if going to remount
%cd
# drive.mount documentation can be accessed via: drive.mount?
#Signature: drive.mount(mountpoint, force_remount=False, timeout_ms=120000, use_metadata_server=False)
google.colab.drive.mount(os.path.join("/content/",mountpoint_folder_name), force_remount=True) # mounts to a folder called mountpoint_folder_name
if project_path_full not in sys.path:
pass
#sys.path.insert(0,project_path_full)
%cd {project_path_full}
except ModuleNotFoundError: # in case not run in Google colab
import traceback
traceback.print_exc()
/root Mounted at /content/gdrive /content/gdrive/.shortcut-targets-by-id/1okL5s1HTQUWqKodPSVNP_RsK0o81IH5E/PythonProjects/GeospatialAnalysis
#@title ## Option 2) Clone project files from Github
!git clone https://github.com/ryerrabelli/GeospatialAnalysis.git
project_path_full = os.path.join("/content/","GeospatialAnalysis")
sys.path.insert(1,project_path_full)
%cd GeospatialAnalysis
print(sys.path)
Cloning into 'GeospatialAnalysis'... remote: Enumerating objects: 58, done. remote: Counting objects: 100% (58/58), done. remote: Compressing objects: 100% (43/43), done. remote: Total 58 (delta 15), reused 54 (delta 11), pack-reused 0 Unpacking objects: 100% (58/58), done. ['', '/content/GeospatialAnalysis', '/content', '/env/python', '/usr/lib/python37.zip', '/usr/lib/python3.7', '/usr/lib/python3.7/lib-dynload', '/usr/local/lib/python3.7/dist-packages', '/usr/lib/python3/dist-packages', '/usr/local/lib/python3.7/dist-packages/IPython/extensions', '/root/.ipython']
image_folder_path = "outputs"
if not os.path.exists(image_folder_path):
os.mkdir(image_folder_path)
def save_figure(fig, file_name:str, animated=False):
"""
fig is of type plotly.graph_objs._figure.Figure,
Requires kaleido to be installed
"""
fig.write_html(os.path.join(image_folder_path, file_name+".html"))
if not animated:
fig.write_image(os.path.join(image_folder_path, file_name+".svg"))
fig.write_image(os.path.join(image_folder_path, file_name+".png"))
fig.write_image(os.path.join(image_folder_path, file_name+".jpg"))
from urllib.request import urlopen
import json
with urlopen('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json') as response:
counties = json.load(response)
for k,v in counties.items():
print(k,": ",v)
IOPub data rate exceeded. The notebook server will temporarily stop sending output to the client in order to avoid crashing it. To change this limit, set the config variable `--NotebookApp.iopub_data_rate_limit`. Current values: NotebookApp.iopub_data_rate_limit=1000000.0 (bytes/sec) NotebookApp.rate_limit_window=3.0 (secs)
dfA = pd.read_csv("data/2022_04_10 ent initial output.csv", dtype={"FIPS": str}) # gets per county info
dfB = pd.read_csv("data/2022_05_05 sums and slopes ent.csv", dtype={"HCPCS Code": str}) # gets per healthcare code info
display(dfA.info())
display(dfB.info())
<class 'pandas.core.frame.DataFrame'> RangeIndex: 941 entries, 0 to 940 Data columns (total 72 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 FIPS 941 non-null object 1 Total Number of Services 941 non-null float64 2 Total Medicare Payment Amount 941 non-null float64 3 Total Number of Services: 2019 941 non-null float64 4 Total Medicare Payment Amount: 2019 941 non-null float64 5 Total Number of Services: 2018 941 non-null float64 6 Total Medicare Payment Amount: 2018 941 non-null float64 7 Total Number of Services: 2017 941 non-null float64 8 Total Medicare Payment Amount: 2017 941 non-null float64 9 Total Number of Services: 2016 941 non-null float64 10 Total Medicare Payment Amount: 2016 941 non-null float64 11 Total Number of Services: 2015 941 non-null float64 12 Total Medicare Payment Amount: 2015 941 non-null float64 13 tot_ratio 941 non-null float64 14 % ASC Procedures: 2019 941 non-null float64 15 % ASC Billing: 2019 941 non-null float64 16 % ASC Procedures: 2018 941 non-null float64 17 % ASC Billing: 2018 941 non-null float64 18 % ASC Procedures: 2017 941 non-null float64 19 % ASC Billing: 2017 941 non-null float64 20 % ASC Procedures: 2016 941 non-null float64 21 % ASC Billing: 2016 941 non-null float64 22 % ASC Procedures: 2015 941 non-null float64 23 % ASC Billing: 2015 941 non-null float64 24 % ASC Procedures 941 non-null float64 25 % ASC Billing 941 non-null float64 26 Beneficiaries with Part A and Part B 941 non-null float64 27 Average Age 941 non-null float64 28 Percent Male 941 non-null float64 29 Percent Non-Hispanic White 941 non-null float64 30 Percent African American 941 non-null float64 31 Percent Hispanic 941 non-null float64 32 Percent Eligible for Medicaid 941 non-null float64 33 Average HCC Score 941 non-null float64 34 Hospital Readmission Rate 941 non-null float64 35 Emergency Department Visits per 1000 Beneficiaries 941 non-null float64 36 Procedures Per Capita Standardized Costs 941 non-null float64 37 Procedure Events Per 1000 Beneficiaries 941 non-null float64 38 metro 941 non-null int64 39 pct_poverty 941 non-null float64 40 median_house_income 941 non-null float64 41 pop 941 non-null float64 42 2013_Rural_urban_cont_code 941 non-null float64 43 Pct_wthout_high_diploma 941 non-null float64 44 Pct_wth_high_diploma 941 non-null float64 45 Pct_wth_some_coll 941 non-null float64 46 Pct_wth_coll_degree 941 non-null float64 47 unemployment 941 non-null float64 48 pct_uninsured 941 non-null float64 49 fibro 941 non-null float64 50 tabacco 941 non-null float64 51 obesity 941 non-null float64 52 migrane 941 non-null float64 53 Alzheimers 941 non-null float64 54 Depression 941 non-null float64 55 Alcohol Abuse 941 non-null float64 56 Drug Abuse 941 non-null float64 57 Schizo_othr_psych 941 non-null float64 58 COPD 941 non-null float64 59 Chronic Kidney Disease 941 non-null float64 60 Osteoporosis 941 non-null float64 61 Stroke 941 non-null float64 62 Diabetes 941 non-null float64 63 Asthma 941 non-null float64 64 Arthritis 941 non-null float64 65 Hypertension 941 non-null float64 66 Heart Failure 941 non-null float64 67 Ischemic Heart Disease 941 non-null float64 68 Population Density 941 non-null float64 69 Medicare Population Density 941 non-null float64 70 Moran I score for ACS billing fraction 941 non-null object 71 County 941 non-null object dtypes: float64(68), int64(1), object(3) memory usage: 529.4+ KB
None
<class 'pandas.core.frame.DataFrame'> RangeIndex: 53 entries, 0 to 52 Data columns (total 17 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 HCPCS Code 53 non-null int64 1 Total Number of Services 53 non-null float64 2 Total Medicare Payment Amount 53 non-null float64 3 Total Number of Services: 2019 53 non-null float64 4 Total Medicare Payment Amount: 2019 53 non-null float64 5 Total Number of Services: 2018 53 non-null float64 6 Total Medicare Payment Amount: 2018 53 non-null float64 7 Total Number of Services: 2017 53 non-null float64 8 Total Medicare Payment Amount: 2017 53 non-null float64 9 Total Number of Services: 2016 53 non-null float64 10 Total Medicare Payment Amount: 2016 53 non-null float64 11 Total Number of Services: 2015 53 non-null float64 12 Total Medicare Payment Amount: 2015 53 non-null float64 13 Total Medicare Payment Amount: Slope 53 non-null float64 14 Total Medicare Payment Amount: Pearson Coef 53 non-null float64 15 Total Number of Services: Slope 53 non-null float64 16 Total Number of Services: Pearson Coef 53 non-null float64 dtypes: float64(16), int64(1) memory usage: 7.2 KB
None
dfB["HCPCS Code"]
0 11042 1 11642 2 11643 3 14040 4 14041 5 14060 6 14301 7 15004 8 15120 9 15260 10 15730 11 20926 12 21235 13 30130 14 30140 15 30465 16 30520 17 30802 18 30930 19 31231 20 31237 21 31238 22 31240 23 31253 24 31254 25 31256 26 31257 27 31259 28 31267 29 31276 30 31288 31 31525 32 31526 33 31535 34 31536 35 31541 36 31571 37 31575 38 31579 39 31622 40 38510 41 42415 42 42826 43 43191 44 43200 45 60220 46 60240 47 60500 48 69433 49 69436 50 69631 51 69801 52 69930 Name: HCPCS Code, dtype: int64
for ind in range(max(len(dfA.columns),len(dfB.columns))):
dfAstr = str(dfA.columns[ind])
print(dfAstr, " "*(60-len(dfAstr)),
(dfB.columns[ind] if ind < len(dfB.columns) else "x" ))
FIPS HCPCS Code Total Number of Services Total Number of Services Total Medicare Payment Amount Total Medicare Payment Amount Total Number of Services: 2019 Total Number of Services: 2019 Total Medicare Payment Amount: 2019 Total Medicare Payment Amount: 2019 Total Number of Services: 2018 Total Number of Services: 2018 Total Medicare Payment Amount: 2018 Total Medicare Payment Amount: 2018 Total Number of Services: 2017 Total Number of Services: 2017 Total Medicare Payment Amount: 2017 Total Medicare Payment Amount: 2017 Total Number of Services: 2016 Total Number of Services: 2016 Total Medicare Payment Amount: 2016 Total Medicare Payment Amount: 2016 Total Number of Services: 2015 Total Number of Services: 2015 Total Medicare Payment Amount: 2015 Total Medicare Payment Amount: 2015 tot_ratio Total Medicare Payment Amount: Slope % ASC Procedures: 2019 Total Medicare Payment Amount: Pearson Coef % ASC Billing: 2019 Total Number of Services: Slope % ASC Procedures: 2018 Total Number of Services: Pearson Coef % ASC Billing: 2018 x % ASC Procedures: 2017 x % ASC Billing: 2017 x % ASC Procedures: 2016 x % ASC Billing: 2016 x % ASC Procedures: 2015 x % ASC Billing: 2015 x % ASC Procedures x % ASC Billing x Beneficiaries with Part A and Part B x Average Age x Percent Male x Percent Non-Hispanic White x Percent African American x Percent Hispanic x Percent Eligible for Medicaid x Average HCC Score x Hospital Readmission Rate x Emergency Department Visits per 1000 Beneficiaries x Procedures Per Capita Standardized Costs x Procedure Events Per 1000 Beneficiaries x metro x pct_poverty x median_house_income x pop x 2013_Rural_urban_cont_code x Pct_wthout_high_diploma x Pct_wth_high_diploma x Pct_wth_some_coll x Pct_wth_coll_degree x unemployment x pct_uninsured x fibro x tabacco x obesity x migrane x Alzheimers x Depression x Alcohol Abuse x Drug Abuse x Schizo_othr_psych x COPD x Chronic Kidney Disease x Osteoporosis x Stroke x Diabetes x Asthma x Arthritis x Hypertension x Heart Failure x Ischemic Heart Disease x Population Density x Medicare Population Density x Moran I score for ACS billing fraction x County x
To understand what is meant by long type and wife type dataframes, see https://towardsdatascience.com/visualization-with-plotly-express-comprehensive-guide-eb5ee4b50b57
df_bill_orig = pd.read_csv("data/2022_05_05 sums and slopes ent with HCPCS descriptions.csv",
dtype={
"HCPCS Code": str,
"Total Number of Services": np.int64,
**{f"Total Number of Services: {year}": np.int64 for year in range(2015,2019+1)}
}) # gets per healthcare code info
df_bill_orig.head(2)
| HCPCS Code | HCPCS Description | Total Number of Services | Total Medicare Payment Amount | Total Number of Services: 2019 | Total Medicare Payment Amount: 2019 | Total Number of Services: 2018 | Total Medicare Payment Amount: 2018 | Total Number of Services: 2017 | Total Medicare Payment Amount: 2017 | Total Number of Services: 2016 | Total Medicare Payment Amount: 2016 | Total Number of Services: 2015 | Total Medicare Payment Amount: 2015 | Total Medicare Payment Amount: Slope | Total Medicare Payment Amount: Pearson Coef | Total Number of Services: Slope | Total Number of Services: Pearson Coef | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 11042 | Removal of skin and tissue first 20 sq cm or less | 46073 | 4237372.20 | 6537 | 644733.10 | 11483 | 1317431.90 | 14608 | 1165644.85 | 8266 | 600263.11 | 5179 | 509299.24 | 0.000002 | 0.425707 | 0.000101 | 0.245111 |
| 1 | 11642 | Removal of malignant growth (1.1 to 2.0 centim... | 14749 | 1201135.31 | 2634 | 214496.56 | 2585 | 213254.24 | 2923 | 238474.17 | 3055 | 245483.24 | 3552 | 289427.10 | -0.000047 | -0.929111 | -0.003798 | -0.935792 |
df_bill_wide = df_bill_orig.set_index(["HCPCS Code", "HCPCS Description"])
# Rename the columns so they can be split easier. The 20 is the first two digits of the year columns
df_bill_wide.columns = [col.replace(": ",": : ").replace(": 20","Annual: 20") for col in df_bill_wide.columns]
# Multiindex
df_bill_wide.columns = pd.MultiIndex.from_tuples([tuple(col.split(": ")) if ":" in col else (col,"","Sum") for col in df_bill_wide.columns], names=["Category","Stat","Year"])
df_bill_wide = df_bill_wide[sorted(df_bill_wide)] # rearrange cols alphabetically
df_bill_wide = df_bill_wide.sort_values(by=("Total Number of Services","","Sum"), ascending=False) # sort rows by volume
categories = df_bill_wide.columns.levels[0] #["Total Number of Services", "Total Medicare Payment Amount"]
The slope given in the csv file is actually the inverse slope. We need to either recalculate it or
def calc_slope(y, x):
a = scipy.stats.linregress(x, y=y)
return {"Slope":a.slope, "Pearson Coef":a.rvalue, "Intercept": a.intercept, "P":a.pvalue}
Index(['Total Medicare Payment Amount', 'Total Number of Services'], dtype='object', name='Category')
for category in categories:
new_df = df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )
df_bill_wide[[(category,"",new_col) for new_col in new_df.columns ]]=new_df
#df_bill_wide[(category,"","Slope")]=df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1)
df_bill_wide = df_bill_wide[sorted(df_bill_wide.columns)] # rearrange cols alphabetically
/var/folders/1h/c6vmx9jd2lg6rj_wkfdl22pr0000gn/T/ipykernel_62791/342045181.py:2: PerformanceWarning: indexing past lexsort depth may impact performance. new_df = df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )
df_bill_wide.loc[["14060",
"69930",
"31267",
"30520",
"30140",
"15260",
"14301",
"14040",
"31575",
"31276",
"14041",
"60500",
"31231",
"31579",
"69436",
"31256",
"31525",
"11042",
"31237"]]
| Category | Total Medicare Payment Amount | Total Number of Services | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Year | 2015 | 2016 | 2017 | 2018 | 2019 | Pearson Coef | Slope | Total | 2015 | 2016 | 2017 | 2018 | 2019 | Pearson Coef | Slope | Total | |
| HCPCS Code | HCPCS Description | ||||||||||||||||
| 14060 | Tissue transfer repair of wound (10 sq centimeters or less) of eyelids, nose, ears, and/or lips | 7253164.21 | 7562395.26 | 6718042.68 | 7190368.43 | 6664704.64 | -0.644093 | -2.680000e-06 | 35388675.22 | 13950 | 14043 | 13157 | 13033 | 12781 | -0.931226 | -0.002590 | 66964 |
| 69930 | Implantation of cochlear device | 4164557.97 | 4694007.58 | 6277113.34 | 8438188.15 | 9406753.83 | 0.983619 | 6.800000e-07 | 32980620.87 | 1626 | 1818 | 2191 | 2587 | 2677 | 0.984317 | 0.003375 | 10899 |
| 31267 | Removal of nasal sinus tissue using an endoscope | 2454520.71 | 4313264.94 | 4546272.86 | 5081500.91 | 5474723.78 | 0.923983 | 1.250000e-06 | 21870283.20 | 7561 | 8394 | 9166 | 9227 | 10126 | 0.976416 | 0.001599 | 44474 |
| 30520 | Reshaping of nasal cartilage | 3822391.75 | 3510467.18 | 3887689.27 | 4454847.45 | 4932682.76 | 0.882351 | 2.460000e-06 | 20608078.41 | 9409 | 9508 | 10270 | 10119 | 10934 | 0.933139 | 0.002378 | 50240 |
| 30140 | Removal of nasal air passage | 4090571.97 | 4163402.15 | 4804703.77 | 3414962.46 | 3699636.24 | -0.459544 | -1.380000e-06 | 20173276.59 | 9942 | 11063 | 12663 | 13489 | 14256 | 0.990276 | 0.000887 | 61413 |
| 15260 | Relocation of patient skin to nose, ears, eyelids, and/or lips (20 sq centimeters or less) | 3816163.30 | 4358891.55 | 3827316.88 | 3987570.86 | 3502561.56 | -0.506593 | -2.570000e-06 | 19492504.15 | 7255 | 7518 | 7164 | 6646 | 6439 | -0.885195 | -0.003129 | 35022 |
| 14301 | Tissue transfer repair of wound (30.1 to 60.0 sq centimeters) | 3422400.08 | 3325471.33 | 3811793.74 | 4247217.10 | 4435581.13 | 0.951445 | 3.070000e-06 | 19242463.38 | 4204 | 4516 | 4544 | 4910 | 5027 | 0.975466 | 0.004664 | 23201 |
| 14040 | Tissue transfer repair of wound (10 sq centimeters or less) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet | 4175118.98 | 3907715.69 | 3725634.72 | 3471107.63 | 3582572.68 | -0.921821 | -5.240000e-06 | 18862149.70 | 8379 | 7635 | 7356 | 6475 | 6854 | -0.907694 | -0.001957 | 36699 |
| 31575 | Diagnostic examination of voice box using flexible endoscope | 4095655.56 | 3367114.38 | 2963951.91 | 3007322.23 | 3164731.95 | -0.760895 | -2.610000e-06 | 16598776.03 | 69604 | 58104 | 58140 | 59663 | 62112 | -0.442025 | -0.000146 | 307623 |
| 31276 | Exploration of nasal sinus using an endoscope | 3551553.36 | 3919275.81 | 4510942.92 | 1568753.14 | 1926164.94 | -0.690081 | -8.500000e-07 | 15476690.17 | 5855 | 6240 | 7061 | 3202 | 3838 | -0.680085 | -0.000654 | 26196 |
| 14041 | Tissue transfer repair of wound (10.1 to 30.0 sq centimeters) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet | 2481173.43 | 2174377.77 | 2237142.30 | 2455397.22 | 2448822.04 | 0.240025 | 2.660000e-06 | 11796912.76 | 4486 | 3871 | 4066 | 4205 | 4311 | -0.010797 | -0.000073 | 20939 |
| 60500 | Removal or exploration of parathyroid glands | 1466292.92 | 1408770.07 | 1410847.34 | 1862490.74 | 2197618.10 | 0.863679 | 3.890000e-06 | 8346019.17 | 1960 | 1870 | 1798 | 2272 | 2644 | 0.800555 | 0.003621 | 10544 |
| 31231 | Diagnostic examination of nasal passages using an endoscope | 1561172.64 | 1473524.08 | 1495479.72 | 1726503.13 | 1865779.80 | 0.814438 | 7.690000e-06 | 8122459.37 | 33396 | 32245 | 32767 | 38601 | 42188 | 0.866909 | 0.000314 | 179197 |
| 31579 | Examination to assess movement of vocal cord flaps using an endoscope | 1329457.32 | 1080066.04 | 957141.66 | 1213382.40 | 1418448.74 | 0.264974 | 2.260000e-06 | 5998496.16 | 11995 | 9957 | 10470 | 13064 | 15023 | 0.708342 | 0.000548 | 60509 |
| 69436 | Incision of eardrum with insertion of eardrum tube under general anesthesia | 1291093.18 | 1541750.79 | 989017.79 | 1045676.06 | 1013430.98 | -0.700318 | -4.660000e-06 | 5880968.80 | 4371 | 4125 | 3889 | 4011 | 4032 | -0.694376 | -0.006088 | 20428 |
| 31256 | Incision of nasal sinus using an endoscope | 1021363.93 | 1035079.99 | 1059938.69 | 913926.80 | 991835.22 | -0.506544 | -1.420000e-05 | 5022144.63 | 3599 | 3847 | 3956 | 3242 | 3465 | -0.479295 | -0.002631 | 18109 |
| 31525 | Diagnostic examination of voice box using an endoscope | 548628.32 | 783501.65 | 700741.37 | 1022976.64 | 1268959.66 | 0.936517 | 5.220000e-06 | 4324807.64 | 4231 | 5918 | 5299 | 7722 | 9470 | 0.935413 | 0.000712 | 32640 |
| 11042 | Removal of skin and tissue first 20 sq cm or less | 509299.24 | 600263.11 | 1165644.85 | 1317431.90 | 644733.10 | 0.425707 | 1.830000e-06 | 4237372.20 | 5179 | 8266 | 14608 | 11483 | 6537 | 0.245111 | 0.000101 | 46073 |
| 31237 | Biopsy or removal of nasal polyp or tissue using an endoscope | 689656.89 | 700877.99 | 831306.55 | 817973.23 | 945048.46 | 0.943382 | 1.420000e-05 | 3984863.12 | 4011 | 3961 | 4811 | 4766 | 5431 | 0.935600 | 0.002402 | 22980 |
df_bill_wide.loc[["31575","31237"]]
| Category | Total Medicare Payment Amount | Total Number of Services | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Year | 2015 | 2016 | 2017 | 2018 | 2019 | Pearson Coef | Slope | Total | 2015 | 2016 | 2017 | 2018 | 2019 | Pearson Coef | Slope | Total | |
| HCPCS Code | HCPCS Description | ||||||||||||||||
| 31575 | Diagnostic examination of voice box using flexible endoscope | 4095655.56 | 3367114.38 | 2963951.91 | 3007322.23 | 3164731.95 | -0.760895 | -0.000003 | 16598776.03 | 69604 | 58104 | 58140 | 59663 | 62112 | -0.442025 | -0.000146 | 307623 |
| 31237 | Biopsy or removal of nasal polyp or tissue using an endoscope | 689656.89 | 700877.99 | 831306.55 | 817973.23 | 945048.46 | 0.943382 | 0.000014 | 3984863.12 | 4011 | 3961 | 4811 | 4766 | 5431 | 0.935600 | 0.002402 | 22980 |
A = sklearn.linear_model.LinearRegression()
B = A.fit(np.arange(2015,2019+1).reshape(-1,1), df_bill_wide.loc["14060"][("Total Number of Services","Annual")].values.reshape(-1,1) )
B.coef_, B.intercept_
(array([[-334.8]]), array([688684.4]))
import statsmodels.api as sm
df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )
X = sm.add_constant(np.arange(2015,2019+1)) # adding a constant
Y = df_bill_wide.loc["14060"][("Total Number of Services","Annual")].values.reshape(-1,1)
model = sm.OLS(Y, X).fit()
predictions = model.predict(X)
print_model = model.summary()
/Users/ryerrabelli/.conda/envs/GeospatialAnalysis/lib/python3.8/site-packages/statsmodels/stats/stattools.py:74: ValueWarning: omni_normtest is not valid with less than 8 observations; 5 samples were given.
warn("omni_normtest is not valid with less than 8 observations; %i "
print_model
| Dep. Variable: | y | R-squared: | 0.867 |
|---|---|---|---|
| Model: | OLS | Adj. R-squared: | 0.823 |
| Method: | Least Squares | F-statistic: | 19.59 |
| Date: | Wed, 22 Jun 2022 | Prob (F-statistic): | 0.0214 |
| Time: | 17:45:16 | Log-Likelihood: | -33.205 |
| No. Observations: | 5 | AIC: | 70.41 |
| Df Residuals: | 3 | BIC: | 69.63 |
| Df Model: | 1 | ||
| Covariance Type: | nonrobust |
| coef | std err | t | P>|t| | [0.025 | 0.975] | |
|---|---|---|---|---|---|---|
| const | 6.887e+05 | 1.53e+05 | 4.514 | 0.020 | 2.03e+05 | 1.17e+06 |
| x1 | -334.8000 | 75.648 | -4.426 | 0.021 | -575.546 | -94.054 |
| Omnibus: | nan | Durbin-Watson: | 3.135 |
|---|---|---|---|
| Prob(Omnibus): | nan | Jarque-Bera (JB): | 0.361 |
| Skew: | 0.535 | Prob(JB): | 0.835 |
| Kurtosis: | 2.232 | Cond. No. | 2.88e+06 |
with pd.option_context('display.float_format', '{:,.2f}'.format):
display(df_bill_wide)
| Category | Total Medicare Payment Amount | Total Number of Services | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Stat | Annual | Annual | |||||||||||||||||||
| Year | Intercept | P | Pearson Coef | Slope | Sum | 2015 | 2016 | 2017 | 2018 | 2019 | Intercept | P | Pearson Coef | Slope | Sum | 2015 | 2016 | 2017 | 2018 | 2019 | |
| HCPCS Code | HCPCS Description | ||||||||||||||||||||
| 31575 | Diagnostic examination of voice box using flexible endoscope | 451,424,416.13 | 0.14 | -0.76 | -222,163.94 | 16,598,776.03 | 4,095,655.56 | 3,367,114.38 | 2,963,951.91 | 3,007,322.23 | 3,164,731.95 | 2,769,347.10 | 0.46 | -0.44 | -1,342.50 | 307623 | 69604 | 58104 | 58140 | 59663 | 62112 |
| 31231 | Diagnostic examination of nasal passages using an endoscope | -172,279,910.86 | 0.09 | 0.81 | 86,219.34 | 8,122,459.37 | 1,561,172.64 | 1,473,524.08 | 1,495,479.72 | 1,726,503.13 | 1,865,779.80 | -4,792,858.60 | 0.06 | 0.87 | 2,394.00 | 179197 | 33396 | 32245 | 32767 | 38601 | 42188 |
| 14060 | Tissue transfer repair of wound (10 sq centimeters or less) of eyelids, nose, ears, and/or lips | 319,500,137.19 | 0.24 | -0.64 | -154,894.60 | 35,388,675.22 | 7,253,164.21 | 7,562,395.26 | 6,718,042.68 | 7,190,368.43 | 6,664,704.64 | 688,684.40 | 0.02 | -0.93 | -334.80 | 66964 | 13950 | 14043 | 13157 | 13033 | 12781 |
| 30140 | Removal of nasal air passage | 312,698,414.27 | 0.44 | -0.46 | -153,031.11 | 20,173,276.59 | 4,090,571.97 | 4,163,402.15 | 4,804,703.77 | 3,414,962.46 | 3,699,636.24 | -2,217,309.20 | 0.00 | 0.99 | 1,105.40 | 61413 | 9942 | 11063 | 12663 | 13489 | 14256 |
| 31579 | Examination to assess movement of vocal cord flaps using an endoscope | -61,589,349.41 | 0.67 | 0.26 | 31,129.92 | 5,998,496.16 | 1,329,457.32 | 1,080,066.04 | 957,141.66 | 1,213,382.40 | 1,418,448.74 | -1,836,075.30 | 0.18 | 0.71 | 916.30 | 60509 | 11995 | 9957 | 10470 | 13064 | 15023 |
| 30520 | Reshaping of nasal cartilage | -634,251,278.21 | 0.05 | 0.88 | 316,496.23 | 20,608,078.41 | 3,822,391.75 | 3,510,467.18 | 3,887,689.27 | 4,454,847.45 | 4,932,682.76 | -728,375.70 | 0.02 | 0.93 | 366.10 | 50240 | 9409 | 9508 | 10270 | 10119 | 10934 |
| 11042 | Removal of skin and tissue first 20 sq cm or less | -198,439,489.63 | 0.47 | 0.43 | 98,803.65 | 4,237,372.20 | 509,299.24 | 600,263.11 | 1,165,644.85 | 1,317,431.90 | 644,733.10 | -1,187,471.50 | 0.69 | 0.25 | 593.30 | 46073 | 5179 | 8266 | 14608 | 11483 | 6537 |
| 31267 | Removal of nasal sinus tissue using an endoscope | -1,368,929,056.95 | 0.02 | 0.92 | 680,864.21 | 21,870,283.20 | 2,454,520.71 | 4,313,264.94 | 4,546,272.86 | 5,081,500.91 | 5,474,723.78 | -1,193,842.30 | 0.00 | 0.98 | 596.30 | 44474 | 7561 | 8394 | 9166 | 9227 | 10126 |
| 14040 | Tissue transfer repair of wound (10 sq centimeters or less) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet | 330,869,453.06 | 0.03 | -0.92 | -162,170.07 | 18,862,149.70 | 4,175,118.98 | 3,907,715.69 | 3,725,634.72 | 3,471,107.63 | 3,582,572.68 | 856,496.80 | 0.03 | -0.91 | -421.00 | 36699 | 8379 | 7635 | 7356 | 6475 | 6854 |
| 15260 | Relocation of patient skin to nose, ears, eyelids, and/or lips (20 sq centimeters or less) | 205,300,825.92 | 0.38 | -0.51 | -99,852.42 | 19,492,504.15 | 3,816,163.30 | 4,358,891.55 | 3,827,316.88 | 3,987,570.86 | 3,502,561.56 | 512,061.20 | 0.05 | -0.89 | -250.40 | 35022 | 7255 | 7518 | 7164 | 6646 | 6439 |
| 31525 | Diagnostic examination of voice box using an endoscope | -338,018,806.51 | 0.02 | 0.94 | 168,013.77 | 4,324,807.64 | 548,628.32 | 783,501.65 | 700,741.37 | 1,022,976.64 | 1,268,959.66 | -2,470,751.40 | 0.02 | 0.94 | 1,228.20 | 32640 | 4231 | 5918 | 5299 | 7722 | 9470 |
| 31276 | Exploration of nasal sinus using an endoscope | 1,132,877,449.20 | 0.20 | -0.69 | -560,129.95 | 15,476,690.17 | 3,551,553.36 | 3,919,275.81 | 4,510,942.92 | 1,568,753.14 | 1,926,164.94 | 1,431,661.60 | 0.21 | -0.68 | -707.20 | 26196 | 5855 | 6240 | 7061 | 3202 | 3838 |
| 14301 | Tissue transfer repair of wound (30.1 to 60.0 sq centimeters) | -590,784,864.70 | 0.01 | 0.95 | 294,810.79 | 19,242,463.38 | 3,422,400.08 | 3,325,471.33 | 3,811,793.74 | 4,247,217.10 | 4,435,581.13 | -406,827.80 | 0.00 | 0.98 | 204.00 | 23201 | 4204 | 4516 | 4544 | 4910 | 5027 |
| 31237 | Biopsy or removal of nasal polyp or tissue using an endoscope | -125,846,096.62 | 0.02 | 0.94 | 62,787.84 | 3,984,863.12 | 689,656.89 | 700,877.99 | 831,306.55 | 817,973.23 | 945,048.46 | -730,600.50 | 0.02 | 0.94 | 364.50 | 22980 | 4011 | 3961 | 4811 | 4766 | 5431 |
| 14041 | Tissue transfer repair of wound (10.1 to 30.0 sq centimeters) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet | -41,271,689.79 | 0.70 | 0.24 | 21,631.67 | 11,796,912.76 | 2,481,173.43 | 2,174,377.77 | 2,237,142.30 | 2,455,397.22 | 2,448,822.04 | 7,415.00 | 0.99 | -0.01 | -1.60 | 20939 | 4486 | 3871 | 4066 | 4205 | 4311 |
| 69436 | Incision of eardrum with insertion of eardrum tube under general anesthesia | 213,243,398.28 | 0.19 | -0.70 | -105,139.91 | 5,880,968.80 | 1,291,093.18 | 1,541,750.79 | 989,017.79 | 1,045,676.06 | 1,013,430.98 | 163,832.00 | 0.19 | -0.69 | -79.20 | 20428 | 4371 | 4125 | 3889 | 4011 | 4032 |
| 15004 | Preparation of graft site of face, scalp, eyelids, mouth, neck, ears, eye region, genitals, hands, feet, and/or multiple fingers or toes (first 100 sq cm or 1% body area of infants and children) | -74,622,094.70 | 0.01 | 0.97 | 37,284.98 | 2,908,600.22 | 492,031.70 | 549,173.13 | 607,020.35 | 614,663.70 | 645,711.34 | -240,811.60 | 0.16 | 0.74 | 121.40 | 20261 | 3634 | 3988 | 4310 | 4188 | 4141 |
| 31256 | Incision of nasal sinus using an endoscope | 37,352,908.96 | 0.38 | -0.51 | -18,021.06 | 5,022,144.63 | 1,021,363.93 | 1,035,079.99 | 1,059,938.69 | 913,926.80 | 991,835.22 | 179,705.90 | 0.41 | -0.48 | -87.30 | 18109 | 3599 | 3847 | 3956 | 3242 | 3465 |
| 11642 | Removal of malignant growth (1.1 to 2.0 centimeters) of the face, ears, eyelids, nose, or lips | 36,967,796.20 | 0.02 | -0.93 | -18,209.01 | 1,201,135.31 | 289,427.10 | 245,483.24 | 238,474.17 | 213,254.24 | 214,496.56 | 468,070.00 | 0.02 | -0.94 | -230.60 | 14749 | 3552 | 3055 | 2923 | 2585 | 2634 |
| 69930 | Implantation of cochlear device | -2,863,306,906.72 | 0.00 | 0.98 | 1,422,857.23 | 32,980,620.87 | 4,164,557.97 | 4,694,007.58 | 6,277,113.34 | 8,438,188.15 | 9,406,753.83 | -576,900.90 | 0.00 | 0.98 | 287.10 | 10899 | 1626 | 1818 | 2191 | 2587 | 2677 |
| 60500 | Removal or exploration of parathyroid glands | -384,862,832.92 | 0.06 | 0.86 | 191,637.10 | 8,346,019.17 | 1,466,292.92 | 1,408,770.07 | 1,410,847.34 | 1,862,490.74 | 2,197,618.10 | -354,900.20 | 0.10 | 0.80 | 177.00 | 10544 | 1960 | 1870 | 1798 | 2272 | 2644 |
| 31571 | Injection of vocal cords using an endoscope with operating microscope or telescope | 1,664,074.51 | 0.97 | -0.03 | -648.22 | 1,783,053.68 | 337,017.46 | 394,075.85 | 319,825.53 | 402,641.13 | 329,493.71 | 41,240.90 | 0.75 | -0.20 | -19.50 | 9547 | 1894 | 2112 | 1695 | 1987 | 1859 |
| 31622 | Diagnostic examination of lung airways using an endoscope | 10,377,666.75 | 0.03 | -0.90 | -5,055.91 | 899,461.24 | 185,955.57 | 191,174.40 | 178,972.39 | 174,191.34 | 169,167.54 | 155,343.70 | 0.02 | -0.94 | -76.10 | 9250 | 2037 | 1907 | 1836 | 1720 | 1750 |
| 31541 | Removal of growth of tongue and/or vocal cord stripping using an endoscope with operating microscope or telescope | 24,238,302.44 | 0.51 | -0.40 | -11,841.86 | 1,766,354.11 | 428,916.69 | 311,467.78 | 318,524.17 | 364,008.38 | 343,437.09 | 92,173.20 | 0.37 | -0.52 | -45.00 | 7041 | 1635 | 1317 | 1301 | 1439 | 1349 |
| 11643 | Removal of malignant growth (2.1 to 3.0 centimeters) of the face, ears, eyelids, nose, or lips | 16,925,561.91 | 0.05 | -0.88 | -8,327.74 | 642,551.63 | 139,924.42 | 140,722.56 | 128,258.93 | 129,997.44 | 103,648.28 | 135,124.70 | 0.07 | -0.84 | -66.30 | 6988 | 1487 | 1479 | 1414 | 1426 | 1182 |
| 69801 | Incision of fluid canals of inner ear with infusion of drugs, transcanal approach | 21,659,579.29 | 0.08 | -0.84 | -10,679.57 | 594,412.84 | 149,143.78 | 125,889.28 | 101,842.11 | 117,694.22 | 99,843.45 | 224,419.00 | 0.07 | -0.85 | -110.60 | 6694 | 1628 | 1419 | 1184 | 1357 | 1106 |
| 31536 | Biopsy of voice box using an endoscope with operating microscope or telescope | 402,826.01 | 0.99 | -0.01 | -68.77 | 1,320,604.79 | 286,775.12 | 252,209.73 | 235,033.38 | 268,100.83 | 278,485.73 | 10,221.90 | 0.87 | -0.11 | -4.50 | 5727 | 1235 | 1093 | 1071 | 1138 | 1190 |
| 69433 | Incision of eardrum with insertion of eardrum tube under local or topical anesthesia | 2,082,486.27 | 0.88 | -0.10 | -975.09 | 578,678.96 | 137,111.76 | 95,794.52 | 105,930.47 | 119,417.25 | 120,424.96 | 21,645.00 | 0.86 | -0.11 | -10.20 | 5358 | 1272 | 885 | 987 | 1101 | 1113 |
| 30930 | Therapeutic fracture of nasal passages | -8,543,396.98 | 0.58 | 0.34 | 4,366.51 | 1,319,228.10 | 266,267.33 | 230,010.14 | 285,853.59 | 267,984.22 | 269,112.82 | -59,856.00 | 0.18 | 0.71 | 30.20 | 5287 | 1052 | 953 | 1057 | 1091 | 1134 |
| 43200 | Diagnostic examination of esophagus using an endoscope | 15,438,590.04 | 0.01 | -0.95 | -7,621.84 | 326,653.46 | 78,306.83 | 75,608.99 | 63,212.10 | 63,046.87 | 46,478.67 | 353,421.90 | 0.00 | -1.00 | -174.70 | 5260 | 1430 | 1192 | 1037 | 897 | 704 |
| 21235 | Obtaining ear cartilage for grafting | -160,906,618.84 | 0.04 | 0.89 | 80,002.89 | 2,296,051.46 | 268,245.90 | 348,416.66 | 542,354.62 | 589,131.20 | 547,903.08 | 23,230.20 | 0.70 | -0.24 | -11.00 | 5216 | 979 | 1155 | 1076 | 1009 | 997 |
| 31288 | Removal of nasal sinus tissue using an endoscope | 209,977,666.34 | 0.32 | -0.57 | -103,889.11 | 2,166,667.44 | 460,441.25 | 585,987.47 | 820,141.39 | 132,215.78 | 167,881.55 | 692,232.10 | 0.12 | -0.78 | -342.70 | 5031 | 1395 | 1421 | 1705 | 236 | 274 |
| 31254 | Partial removal of nasal sinus using an endoscope | -109,587,389.11 | 0.10 | 0.81 | 54,556.94 | 2,269,834.67 | 278,635.35 | 433,153.49 | 514,211.15 | 551,675.73 | 492,158.95 | -100,084.70 | 0.05 | 0.88 | 50.10 | 4835 | 891 | 890 | 927 | 1081 | 1046 |
| 31535 | Biopsy of voice box using an endoscope | 13,020,560.75 | 0.52 | -0.39 | -6,362.61 | 935,851.67 | 181,200.03 | 232,824.57 | 168,478.11 | 175,099.42 | 178,249.54 | 57,413.60 | 0.22 | -0.67 | -28.00 | 4688 | 963 | 1039 | 881 | 925 | 880 |
| 20926 | Tissue graft | -83,870,910.20 | 0.10 | 0.80 | 41,842.81 | 2,630,208.04 | 426,939.03 | 450,643.34 | 585,782.18 | 610,737.46 | 556,106.03 | -92,062.10 | 0.09 | 0.83 | 46.10 | 4608 | 797 | 865 | 1009 | 954 | 983 |
| 43191 | Diagnostic examination of esophagus using an endoscope | -18,405,794.32 | 0.01 | 0.97 | 9,161.54 | 365,159.32 | 54,382.61 | 67,967.41 | 68,026.51 | 81,217.55 | 93,565.24 | -26,194.60 | 0.58 | 0.34 | 13.40 | 4166 | 777 | 909 | 770 | 823 | 887 |
| 30130 | Removal of nasal air passage | 29,693,651.65 | 0.09 | -0.83 | -14,619.98 | 1,025,739.76 | 227,895.47 | 229,411.98 | 216,179.41 | 165,502.70 | 186,750.20 | 92,643.20 | 0.11 | -0.79 | -45.60 | 3340 | 717 | 762 | 717 | 548 | 596 |
| 30465 | Widening of nasal passage | -156,245,158.57 | 0.10 | 0.81 | 77,759.32 | 2,976,999.79 | 423,255.25 | 460,478.90 | 747,604.40 | 606,739.83 | 738,921.41 | -166,148.70 | 0.06 | 0.86 | 82.70 | 3286 | 483 | 514 | 792 | 687 | 810 |
| 30802 | Destruction of soft tissue in nasal passages | 915,438.12 | 0.97 | -0.02 | -402.28 | 520,146.37 | 93,891.34 | 144,814.17 | 68,066.24 | 98,175.24 | 115,199.38 | -73,211.00 | 0.27 | 0.62 | 36.60 | 3056 | 553 | 609 | 491 | 725 | 678 |
| 60240 | Removal of thyroid | 143,371,216.45 | 0.09 | -0.82 | -70,876.74 | 2,064,149.25 | 559,710.52 | 564,614.42 | 289,389.15 | 325,602.27 | 324,832.89 | 184,122.60 | 0.10 | -0.81 | -91.00 | 2878 | 746 | 793 | 423 | 457 | 459 |
| 31253 | Complete examination of nose and sinuses using an endoscope | -522,371,533.22 | 0.05 | 0.87 | 259,154.08 | 1,711,281.13 | 0.00 | 0.00 | 0.00 | 831,021.41 | 880,259.72 | -776,034.00 | 0.06 | 0.87 | 385.00 | 2555 | 0 | 0 | 0 | 1260 | 1295 |
| 31259 | Removal of tissue from sphenoid sinus using an endoscope | -415,916,300.11 | 0.04 | 0.89 | 206,335.51 | 1,312,137.99 | 0.00 | 0.00 | 0.00 | 560,920.86 | 751,217.13 | -754,285.60 | 0.04 | 0.89 | 374.20 | 2379 | 0 | 0 | 0 | 1016 | 1363 |
| 15120 | Skin graft of face, scalp, eyelids, mouth, neck, ears, eye region, genitals, hands, feet, and/or multiple fingers or toes (first 100 sq cm or less, or 1% body area of infants and children) | -73,318,856.05 | 0.11 | 0.79 | 36,521.74 | 1,727,467.67 | 249,466.87 | 333,222.62 | 322,363.94 | 447,454.72 | 374,959.52 | -38,254.00 | 0.32 | 0.57 | 19.20 | 2362 | 392 | 498 | 466 | 538 | 468 |
| 31238 | Control of nasal bleeding using an endoscope | 17,320,326.24 | 0.18 | -0.71 | -8,551.92 | 355,548.25 | 103,707.75 | 65,452.13 | 57,244.23 | 70,939.82 | 58,204.32 | 78,079.70 | 0.25 | -0.63 | -38.50 | 2126 | 581 | 379 | 360 | 456 | 350 |
| 31526 | Diagnostic examination of voice box using an endoscope with operating microscope or telescope | 5,827,781.05 | 0.35 | -0.53 | -2,869.05 | 204,525.91 | 49,005.12 | 34,247.57 | 47,429.81 | 44,119.52 | 29,723.89 | 61,335.40 | 0.25 | -0.64 | -30.20 | 2110 | 507 | 369 | 480 | 427 | 327 |
| 60220 | Total removal of thyroid lobe on one side of the neck | -46,324,502.37 | 0.02 | 0.94 | 23,079.72 | 1,136,494.62 | 168,535.09 | 208,492.40 | 242,281.69 | 258,011.07 | 259,174.37 | -45,834.90 | 0.09 | 0.82 | 22.90 | 1772 | 293 | 323 | 394 | 386 | 376 |
| 31257 | Complete examination of nose and sinuses and removal of nasal sinus using an endoscope | -308,640,222.79 | 0.04 | 0.89 | 153,117.15 | 985,363.99 | 0.00 | 0.00 | 0.00 | 439,556.46 | 545,807.53 | -537,793.80 | 0.04 | 0.89 | 266.80 | 1709 | 0 | 0 | 0 | 750 | 959 |
| 42415 | Partial removal of salivary gland growth with release of facial nerve | 26,616,259.72 | 0.56 | -0.35 | -13,081.76 | 1,151,738.89 | 196,315.23 | 287,774.38 | 301,095.61 | 183,520.11 | 183,033.56 | 30,579.80 | 0.58 | -0.33 | -15.00 | 1624 | 273 | 406 | 396 | 296 | 253 |
| 38510 | Biopsy or removal of lymph nodes of neck, open procedure | 18,177,988.93 | 0.25 | -0.64 | -8,957.26 | 555,987.63 | 131,596.26 | 124,915.20 | 113,084.72 | 74,247.77 | 112,143.68 | 12,616.90 | 0.76 | -0.19 | -6.10 | 1566 | 325 | 331 | 339 | 222 | 349 |
| 31240 | Removal of nasal breathing passages using an endoscope | -18,103,251.99 | 0.16 | 0.73 | 9,002.31 | 272,076.72 | 53,348.63 | 27,758.99 | 47,718.67 | 62,021.47 | 81,228.96 | -147,558.10 | 0.01 | 0.96 | 73.30 | 1440 | 180 | 176 | 280 | 339 | 465 |
| 69631 | Repair of eardrum and ear canal with opening to ear bones | -15,821,526.05 | 0.46 | 0.44 | 7,955.82 | 1,126,824.53 | 205,385.67 | 220,079.14 | 249,926.63 | 192,457.49 | 258,975.60 | -14,281.60 | 0.65 | 0.28 | 7.20 | 1204 | 216 | 234 | 290 | 190 | 274 |
| 15730 | Creation of flap graft to midface | -248,745,467.50 | 0.04 | 0.89 | 123,401.01 | 771,838.24 | 0.00 | 0.00 | 0.00 | 309,666.39 | 462,171.85 | -263,255.40 | 0.04 | 0.89 | 130.60 | 824 | 0 | 0 | 0 | 342 | 482 |
| 42826 | Removal of tonsils patient age 12 or over | 1,370.22 | 1.00 | -0.00 | -0.00 | 6,851.10 | 0.00 | 0.00 | 6,851.10 | 0.00 | 0.00 | 2.40 | 1.00 | -0.00 | -0.00 | 12 | 0 | 0 | 12 | 0 | 0 |
print("Total amount of services")
A1 = np.sum(df_bill_wide[("Total Number of Services","Total")])
print("{:,}".format(A1))
print("{:,}".format(A1/5))
print("Total medical payment")
A2 = np.sum(df_bill_wide[("Total Medicare Payment Amount","Total")].astype(np.int64) )
print("{:,}".format(A2))
print("{:,}".format(A2/5))
Total amount of services 1,233,099 246,619.8 Total medical payment 320,317,215 64,063,443.0
pd.columns = pd.MultiIndex.from_tuples([tuple(col.split(":")) if ":" in col else (col,"") for col in df_bill_wide.columns])
[('Total Number of Services', ''),
('Total Medicare Payment Amount', ''),
('Total Number of Services', ' 2019'),
('Total Medicare Payment Amount', ' 2019'),
('Total Number of Services', ' 2018'),
('Total Medicare Payment Amount', ' 2018'),
('Total Number of Services', ' 2017'),
('Total Medicare Payment Amount', ' 2017'),
('Total Number of Services', ' 2016'),
('Total Medicare Payment Amount', ' 2016'),
('Total Number of Services', ' 2015'),
('Total Medicare Payment Amount', ' 2015'),
('Total Medicare Payment Amount', ' Slope'),
('Total Medicare Payment Amount', ' Pearson Coef'),
('Total Number of Services', ' Slope'),
('Total Number of Services', ' Pearson Coef')]
pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"),
("Gasoline", "Ford"),
("Electric", "Tesla"),
("Electric", "Nio")])
fips2county = pd.read_csv("data/fips2county.tsv", sep="\t", comment='#', dtype=str)
# The ent CSV file only contains the counties which are analyzable
df_loc_orig = pd.read_csv("data/2022_04_10 ent initial output.csv", dtype={"FIPS": str})
# Merge with the fips 2 county standard data set
df_loc_wide = pd.merge(left=df_loc_orig, right=fips2county, how="left", left_on='FIPS', right_on='CountyFIPS')
# Insert a county "County, ST" col (i.e. "Freehold, NJ" or "Chicago, IL") for ease
df_loc_wide.insert(1, "County_St", df_loc_wide["CountyName"].astype(str) + ", " + df_loc_wide["StateAbbr"].astype(str))
# Display with all the columns
with pd.option_context('display.max_rows', 3, 'display.max_columns', None):
display(df_loc_wide)
pass
loc_simple = ["FIPS", "CountyName","StateAbbr", "% ASC Billing", "Moran I score for ACS billing fraction"]
df_loc_wide_simple=df_loc_wide[loc_simple]
loc_main = ["FIPS", "County", "StateFIPS", "Total Medicare Payment Amount", "% ASC Procedures", "% ASC Billing", "CountyFIPS_3", "CountyName", "StateName", "CountyFIPS", "StateAbbr", "STATE_COUNTY"]
#a=pd.merge(right=df_loc_orig, left=fips2county, how="outer", right_on='FIPS', left_on='CountyFIPS')
#a=a.loc[:,loc_main]
#df_loc_orig2=df_loc_orig.loc[:,["FIPS","pop","Moran I score for ACS billing fraction","County"]]
| FIPS | County_St | Total Number of Services | Total Medicare Payment Amount | Total Number of Services: 2019 | Total Medicare Payment Amount: 2019 | Total Number of Services: 2018 | Total Medicare Payment Amount: 2018 | Total Number of Services: 2017 | Total Medicare Payment Amount: 2017 | Total Number of Services: 2016 | Total Medicare Payment Amount: 2016 | Total Number of Services: 2015 | Total Medicare Payment Amount: 2015 | tot_ratio | % ASC Procedures: 2019 | % ASC Billing: 2019 | % ASC Procedures: 2018 | % ASC Billing: 2018 | % ASC Procedures: 2017 | % ASC Billing: 2017 | % ASC Procedures: 2016 | % ASC Billing: 2016 | % ASC Procedures: 2015 | % ASC Billing: 2015 | % ASC Procedures | % ASC Billing | Beneficiaries with Part A and Part B | Average Age | Percent Male | Percent Non-Hispanic White | Percent African American | Percent Hispanic | Percent Eligible for Medicaid | Average HCC Score | Hospital Readmission Rate | Emergency Department Visits per 1000 Beneficiaries | Procedures Per Capita Standardized Costs | Procedure Events Per 1000 Beneficiaries | metro | pct_poverty | median_house_income | pop | 2013_Rural_urban_cont_code | Pct_wthout_high_diploma | Pct_wth_high_diploma | Pct_wth_some_coll | Pct_wth_coll_degree | unemployment | pct_uninsured | fibro | tabacco | obesity | migrane | Alzheimers | Depression | Alcohol Abuse | Drug Abuse | Schizo_othr_psych | COPD | Chronic Kidney Disease | Osteoporosis | Stroke | Diabetes | Asthma | Arthritis | Hypertension | Heart Failure | Ischemic Heart Disease | Population Density | Medicare Population Density | Moran I score for ACS billing fraction | County | StateFIPS | CountyFIPS_3 | CountyName | StateName | CountyFIPS | StateAbbr | STATE_COUNTY | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01017 | Chambers, AL | 408.0 | 30064.800000 | 157.0 | 10363.09 | 115.0 | 6360.510000 | 136.0 | 13341.20 | 0.0 | 0.00 | 0.0 | 0.00 | 14.196990 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.000000 | 0.0 | 0.0 | 0.000000 | 0.000000 | 8489.0 | 69.2 | 44.126 | 64.466 | 34.326 | 0.286 | 28.144 | 1.04 | 16.296 | 726.4 | 502.664 | 3765.2 | 0 | 19.76 | 38781.0 | 33660.4 | 6.0 | 18.4 | 36.7 | 31.6 | 13.3 | 4.52 | 11.94 | 23.6 | 11.0 | 22.2 | 2.8 | 11.06 | 13.92 | 2.125 | 3.5 | 3.30 | 14.46 | 25.76 | 4.40 | 3.54 | 34.70 | 4.10 | 35.56 | 67.0 | 16.78 | 31.58 | 56.426908 | 14.230610 | Non Significant | Chambers | 01 | 017 | Chambers | Alabama | 01017 | AL | AL | CHAMBERS |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 940 | 51041 | Chesterfield, VA | 617.0 | 204885.309999 | 111.0 | 47135.49 | 167.0 | 54715.569999 | 187.0 | 53982.17 | 78.0 | 24873.24 | 74.0 | 24178.84 | 34.339788 | 28.828829 | 51.859904 | 47.305389 | 61.493685 | 41.176471 | 61.207895 | 15.384615 | 20.982791 | 0.0 | 0.0 | 32.414911 | 47.027037 | 52776.6 | 72.0 | 44.492 | 79.032 | 15.324 | 1.498 | 8.708 | 0.89 | 16.836 | 597.8 | 663.378 | 5401.4 | 1 | 6.96 | 78863.0 | 343550.6 | 1.0 | 7.7 | 23.6 | 28.3 | 40.5 | 3.42 | 8.98 | 17.6 | 6.8 | 20.0 | 2.8 | 9.76 | 16.92 | 1.550 | 1.6 | 2.82 | 9.02 | 20.84 | 5.48 | 3.50 | 26.98 | 5.34 | 31.40 | 59.0 | 10.50 | 25.18 | 811.606508 | 124.679835 | Non Significant | Chesterfield | 51 | 041 | Chesterfield | Virginia | 51041 | VA | VA | CHESTERFIELD |
941 rows × 80 columns
col_categories = ["Total Number of Services:", "Total Medicare Payment Amount:", "% ASC Procedures:", "% ASC Billing:"]
cols_to_keep = ["FIPS","County_St"] # columns to keep in every subgroup so you can line up extra info later
# Create list of df's to combine later, each df is from melting of one category of columns
df_loc_longs = []
# Convert each type of category to long format in separate dataframes
for col_category in col_categories:
df_loc_long = df_loc_wide.melt(id_vars=cols_to_keep,
var_name="Year",
value_vars=[f"{col_category} {year}" for year in range(2015, 2019 +1)],
value_name=f"{col_category} in Year",
)
df_loc_long["Year"] = df_loc_long["Year"].replace({ f"{col_category} {year}":f"{year}" for year in range(2015, 2019 +1)})
df_loc_longs.append(df_loc_long)
# Merge the separate category dataframes
df_loc_long = df_loc_longs[0]
for ind in range(1,len(df_loc_longs)):
df_loc_long = pd.merge(left=df_loc_long, right=df_loc_longs[ind], how="outer", on=(cols_to_keep+["Year"]) )
# Merge with the overall wide dataframe to keep those other values
df_loc_long = pd.merge(left=df_loc_long,
right=df_loc_wide.drop([f"{col_category} {year}" for year in range(2015, 2019 +1) for col_category in col_categories], axis=1),
how="left", on=cols_to_keep)
display(df_loc_long)
| FIPS | County_St | Year | Total Number of Services: in Year | Total Medicare Payment Amount: in Year | % ASC Procedures: in Year | % ASC Billing: in Year | Total Number of Services | Total Medicare Payment Amount | tot_ratio | ... | Medicare Population Density | Moran I score for ACS billing fraction | County | StateFIPS | CountyFIPS_3 | CountyName | StateName | CountyFIPS | StateAbbr | STATE_COUNTY | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01017 | Chambers, AL | 2015 | 0.0 | 0.00 | 0.000000 | 0.000000 | 408.0 | 30064.800000 | 14.196990 | ... | 14.230610 | Non Significant | Chambers | 01 | 017 | Chambers | Alabama | 01017 | AL | AL | CHAMBERS |
| 1 | 01033 | Colbert, AL | 2015 | 108.0 | 10404.39 | 0.000000 | 0.000000 | 272.0 | 37080.230000 | 16.000000 | ... | 22.681014 | Non Significant | Colbert | 01 | 033 | Colbert | Alabama | 01033 | AL | AL | COLBERT |
| 2 | 01045 | Dale, AL | 2015 | 0.0 | 0.00 | 0.000000 | 0.000000 | 12.0 | 405.210000 | 0.999104 | ... | 17.700437 | Non Significant | Dale | 01 | 045 | Dale | Alabama | 01045 | AL | AL | DALE |
| 3 | 01083 | Limestone, AL | 2015 | 0.0 | 0.00 | 0.000000 | 0.000000 | 55.0 | 9515.590000 | 4.000000 | ... | 29.157261 | Non Significant | Limestone | 01 | 083 | Limestone | Alabama | 01083 | AL | AL | LIMESTONE |
| 4 | 05145 | White, AR | 2015 | 1217.0 | 48412.57 | 0.000000 | 0.000000 | 1269.0 | 52190.220000 | 11.995594 | ... | 15.224018 | Non Significant | White | 05 | 145 | White | Arkansas | 05145 | AR | AR | WHITE |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 4700 | 21073 | Franklin, KY | 2019 | 0.0 | 0.00 | 0.000000 | 0.000000 | 114.0 | 7749.960000 | 3.910144 | ... | 62.858188 | Non Significant | Franklin | 21 | 073 | Franklin | Kentucky | 21073 | KY | KY | FRANKLIN |
| 4701 | 56021 | Laramie, WY | 2019 | 422.0 | 79083.86 | 100.000000 | 100.000000 | 1784.0 | 337949.890001 | 21.000000 | ... | 6.286729 | Non Significant | Laramie | 56 | 021 | Laramie | Wyoming | 56021 | WY | WY | LARAMIE |
| 4702 | 54041 | Lewis, WV | 2019 | 0.0 | 0.00 | 0.000000 | 0.000000 | 606.0 | 26648.230000 | 4.000000 | ... | 10.524948 | Low-Low | Lewis | 54 | 041 | Lewis | West Virginia | 54041 | WV | WV | LEWIS |
| 4703 | 50027 | Windsor, VT | 2019 | 319.0 | 12093.61 | 0.000000 | 0.000000 | 1132.0 | 47825.130000 | 35.000000 | ... | 14.320922 | Low-Low | Windsor | 50 | 027 | Windsor | Vermont | 50027 | VT | VT | WINDSOR |
| 4704 | 51041 | Chesterfield, VA | 2019 | 111.0 | 47135.49 | 28.828829 | 51.859904 | 617.0 | 204885.309999 | 34.339788 | ... | 124.679835 | Non Significant | Chesterfield | 51 | 041 | Chesterfield | Virginia | 51041 | VA | VA | CHESTERFIELD |
4705 rows × 65 columns
fig = px.choropleth(df_loc_wide, geojson=counties, locations='FIPS',
color='% ASC Procedures',
color_continuous_scale="Viridis",
#range_color=(0, 12),
scope="usa",
#facet_col="Moran I score for ACS billing fraction",
labels={
"2013_Rural_urban_cont_code":"2013-RUCA",
"pop":"Pop.",
"Average Age":"Mean Age",
"Percent Male":"% M",
"tot_ratio":"Tot. Ratio",
},
hover_name="County_St",
hover_data={
'% ASC Procedures': ":.0f",
"FIPS":True,
"pop": ":.1f",
"2013_Rural_urban_cont_code":True,
"Average Age": ":.1f",
"Percent Male": ":.1f",
},
)
fig.update_layout(
hoverlabel=dict(
bgcolor="white",
font_size=16,
font_family="Rockwell",
align="auto"
)
)
fig.update_layout(legend=dict(
orientation="h",
yanchor="bottom",
y=1.02,
xanchor="right",
x=1
))
# Define layout specificities
fig.update_layout(
margin={"r":0,"t":0,"l":0,"b":0},
title={
'text': f"% ASC Procedures",
'y':0.95,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'
}
)
#fig.show()
save_figure(fig,"choropleth-total")
fig = px.choropleth(df_loc_long, geojson=counties, locations='FIPS',
color='% ASC Procedures: in Year',
color_continuous_scale="Viridis",
#range_color=(0, 12),
scope="usa",
#facet_col="Moran I score for ACS billing fraction",
labels={
"2013_Rural_urban_cont_code":"2013-RUCA",
"pop":"Pop.",
"Average Age":"Mean Age",
"Percent Male":"% M",
"tot_ratio":"Tot. Ratio",
},
hover_name="County_St",
hover_data={
"FIPS":True,
"pop": ":.1f",
"2013_Rural_urban_cont_code":True,
"Average Age": ":.1f",
"Percent Male": ":.1f",
},
animation_frame="Year",
)
fig.update_layout(
hoverlabel=dict(
bgcolor="white",
font_size=16,
font_family="Rockwell",
align="auto"
)
)
fig.update_layout(legend=dict(
orientation="h",
yanchor="bottom",
y=1.02,
xanchor="right",
x=1
))
# Define layout specificities
fig.update_layout(
margin={"r":0,"t":0,"l":0,"b":0},
title={
'text': f"% ASC Procedures by Year",
'y':0.95,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'
}
)
fig.show()
save_figure(fig,"choropleth-all", animated=True)